package jwanalytics.dbloader;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.Statement;

import jwanalytics.dbloader.DB;
import jwanalytics.dbloader.Props;

import org.apache.log4j.Logger;

/** 
 * Populates the table called time_of_day_dimension which is explained on pages 41,53 
 * of "Wiley - The Data Warehouse Toolkit 2nd Edition" .
 * Called from RecreateDatabase
 * @see RecreateDatabase
 */
public class PopulateTimeOfDayDimension {
	
	/** Logger for all pages. */
	public static Logger logger = Logger.getLogger("jwanalytics");
	
	/**
	 * Populates the dimension table.  Uses batch technique for considerable performance improvements.
	 * @param args Not used.
	 */
	public static void main(String[] args) throws Exception {
		//Prepare database connection.
		Connection con  = null;
		Statement st = null;
		Props.init(Props.PROPS_FILE, false);
		try { 
			try { DB.executeStatement(Props.get(Props.PROPS_FILE, "time_of_day_dimension.drop_table_ddl")); } catch(Exception e) { /* ignore drop failed because table did not exist. */ };
			DB.executeStatement(Props.get(Props.PROPS_FILE, "time_of_day_dimension.create_table_ddl"));
			con = DB.getConnection();			
			String ddl = "INSERT INTO time_of_day_dimension (time_of_day_key, hour, minute) VALUES("; //...
			StringBuilder ddlInstance = null;
			st = con.createStatement();
			con.setAutoCommit(false);
			for (int hour = 0; hour < 24; hour++) { //0..23
				for (int minute = 0; minute < 60; minute++) {
					ddlInstance = new StringBuilder(ddl);
					ddlInstance = ddlInstance.append((hour*60+minute)).append(",");
					ddlInstance = ddlInstance.append(hour).append(",");
					ddlInstance = ddlInstance.append(minute).append(")");
					//logger.debug("Executing:"+ddlInstance.toString());
					st.addBatch(ddlInstance.toString());											
				}
				//Every hour (batches of 60).
				st.executeBatch();
				st.clearBatch();
				con.commit();
			}
			
			//Faster to raise keys/indexes after loading data.
			DB.executeStatement(Props.get(Props.PROPS_FILE, "time_of_day_dimension.alter_table_add_primary_key_ddl"));
			DB.executeStatement(Props.get(Props.PROPS_FILE, "time_of_day_dimension.create_index_hour_ddl"));			
		} catch (BatchUpdateException e) {
			logger.error("WAERROR0012:Exception:"+e);	
			logger.error("WAERROR0012cont:"+e.getNextException());
		} finally {
			if (con != null) con.close();
			if (st != null) st.close();
		}		
	}

}
